package com.itheima.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.vo.OperationalData;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface OperationalDataMapper extends BaseMapper<OperationalData> {

    //今日新增会员
    @Select("select count(*) from t_member where create_date = #{today}")
    Integer findTodayNewMember(@Param("today") String today);

    //总会员数
    @Select("select count(*) from t_member")
    Integer findTodayMember();

    //本周新增会员数
    @Select("select count(*) from rpt_date r inner JOIN t_member m \n" +
            "on r.id = m.create_date_id \n" +
            "where fweek = #{weekNum}")
    Integer findThisWeekNewMember(@Param("weekNum") String weekNum);

    //本月新增会员数
    @Select("select count(*) from rpt_date r inner JOIN t_member m \n" +
            "on r.id = m.create_date_id\n" +
            "where fmonth = #{mouthNum}")
    Integer findThisMonthNewMember(@Param("mouthNum") String mouthNum);

    //今日预约数
    @Select("select count(*) from t_order\n" +
            "where order_date = #{today}")
    Integer findTodayOrderNumber(@Param("today") String today);

    //今日到诊数
    @Select("select count(*) from t_order\n" +
            "where order_date = #{today} and order_status='已到诊'")
    Integer findTodayVisitsNumber(@Param("today") String today);

    //本周预约数
    @Select("select count(*) from rpt_date r inner JOIN t_order o \n" +
            "on r.id = o.order_data_id\n" +
            "where fweek = #{weekNum}")
    Integer findThisWeekOrderNumber(@Param("weekNum") String weekNum);



    //本周到诊数
    @Select("select count(*) from rpt_date r inner JOIN t_order o \n" +
            "on r.id = o.order_data_id\n" +
            "where fweek = #{weekNum} and order_status='已到诊' ")
    Integer findThisWeekVisitsNumber(String weekNum);

    //本月预约数
    @Select("select count(*) from rpt_date r inner JOIN t_order o \n" +
            "on r.id = o.order_data_id\n" +
            "where fmonth = #{mouthNum}")
    Integer findThisMonthOrderNumber(@Param("mouthNum") String mouthNum);


    //本月到诊数
    @Select("select count(*) from rpt_date r inner JOIN t_order o \n" +
            "on r.id = o.order_data_id\n" +
            "where fmonth = #{mouthNum} and order_status='已到诊'")
    Integer findThisMonthVisitsNumber(@Param("mouthNum") String mouthNum);

    //热门套餐的查询  前三条
    @Select("select s.name,count(o.id) setmeal_count,count(o.id)/(select count(id) from t_order) proportion \n" +
            "from t_order o, t_setmeal s where o.setmeal_id = s.id \n" +
            "GROUP BY setmeal_id\n" +
            "ORDER BY setmeal_count desc limit 0,3")
    List<Map> findHotSetmeal();

}

